{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import xlwt\n",
    "import xlrd\n",
    "import pymysql   # MySQLdb  不支持python3 用 pymysql\n",
    "import pandas as pd\n",
    "\n",
    "import sqlalchemy"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "# http://www.swsindex.com/idx0530.aspx\n",
    "path = \"F:\\HQData\\申万指数\\SwClass\\\\\"\n",
    "swclass = path + \"SwClassCode_2021.xls\"\n",
    "stock=path + \"StockClassifyUse_stock.xls\"\n",
    "stocks = path + \"最新个股申万行业分类(完整版-截至7月末).xlsx\"\n",
    "stock_tushare = path + \"tushare_stock.csv\"\n",
    "\n",
    "server='rm-bp1witqzl76lpag95.mysql.rds.aliyuncs.com'\n",
    "outserver=\"rm-bp1witqzl76lpag957o.mysql.rds.aliyuncs.com\"\n",
    "aliyun= 'mysql+pymysql://dbuser:dbuser@121@rm-bp1witqzl76lpag957o.mysql.rds.aliyuncs.com:3306/stocks?charset=utf8'\n",
    "\n",
    "user=\"dbuser\"\n",
    "pwd='dbuser@121'\n",
    "dbname=\"stocks\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "data = xlrd.open_workbook(path + '/SwClassCode_2021.xls')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "tables = data.sheets()\n",
    "table = tables[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['Sheet1']"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "names = data.sheet_names()    #返回book中所有工作表的名字\n",
    "names"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "nrows = tables[0].nrows  #获取该sheet中的有效行数"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[text:'行业代码', text:'一级行业名称', text:'二级行业名称', text:'三级行业名称']"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "table = tables[0]\n",
    "row = table.row(0) \n",
    "row"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[text:'110000', text:'农林牧渔', empty:'', empty:'']"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\n",
    "row = table.row(1) \n",
    "row"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>行业代码</th>\n",
       "      <th>一级行业名称</th>\n",
       "      <th>二级行业名称</th>\n",
       "      <th>三级行业名称</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>110000</td>\n",
       "      <td>农林牧渔</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>110100</td>\n",
       "      <td>农林牧渔</td>\n",
       "      <td>种植业</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>110101</td>\n",
       "      <td>农林牧渔</td>\n",
       "      <td>种植业</td>\n",
       "      <td>种子</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>110102</td>\n",
       "      <td>农林牧渔</td>\n",
       "      <td>种植业</td>\n",
       "      <td>粮食种植</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>110103</td>\n",
       "      <td>农林牧渔</td>\n",
       "      <td>种植业</td>\n",
       "      <td>其他种植业</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     行业代码 一级行业名称 二级行业名称 三级行业名称\n",
       "0  110000   农林牧渔    NaN    NaN\n",
       "1  110100   农林牧渔    种植业    NaN\n",
       "2  110101   农林牧渔    种植业     种子\n",
       "3  110102   农林牧渔    种植业   粮食种植\n",
       "4  110103   农林牧渔    种植业  其他种植业"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_sw = pd.read_excel(swclass)\n",
    "df_sw.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "db = pymysql.connect(host=outserver,user=user,password=pwd)\n",
    "dbcmd = db.cursor()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# dbcmd.execute(\"CREATE DATABASE IF not exists stocks\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>market</th>\n",
       "      <th>industrycode</th>\n",
       "      <th>symbol</th>\n",
       "      <th>company_desc</th>\n",
       "      <th>industry_first</th>\n",
       "      <th>industry_seccond</th>\n",
       "      <th>industry_third</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A股</td>\n",
       "      <td>720902</td>\n",
       "      <td>600373.SH</td>\n",
       "      <td>NaN</td>\n",
       "      <td>传媒</td>\n",
       "      <td>出版</td>\n",
       "      <td>大众出版</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>A股</td>\n",
       "      <td>720902</td>\n",
       "      <td>601949.SH</td>\n",
       "      <td>中国出版</td>\n",
       "      <td>传媒</td>\n",
       "      <td>出版</td>\n",
       "      <td>大众出版</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>A股</td>\n",
       "      <td>720902</td>\n",
       "      <td>601801.SH</td>\n",
       "      <td>皖新传媒</td>\n",
       "      <td>传媒</td>\n",
       "      <td>出版</td>\n",
       "      <td>大众出版</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>A股</td>\n",
       "      <td>720902</td>\n",
       "      <td>300788.SZ</td>\n",
       "      <td>中信出版</td>\n",
       "      <td>传媒</td>\n",
       "      <td>出版</td>\n",
       "      <td>大众出版</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>A股</td>\n",
       "      <td>720902</td>\n",
       "      <td>601858.SH</td>\n",
       "      <td>中国科传</td>\n",
       "      <td>传媒</td>\n",
       "      <td>出版</td>\n",
       "      <td>大众出版</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  market  industrycode     symbol company_desc industry_first  \\\n",
       "0     A股        720902  600373.SH          NaN             传媒   \n",
       "1     A股        720902  601949.SH         中国出版             传媒   \n",
       "2     A股        720902  601801.SH         皖新传媒             传媒   \n",
       "3     A股        720902  300788.SZ         中信出版             传媒   \n",
       "4     A股        720902  601858.SH         中国科传             传媒   \n",
       "\n",
       "  industry_seccond industry_third  \n",
       "0               出版           大众出版  \n",
       "1               出版           大众出版  \n",
       "2               出版           大众出版  \n",
       "3               出版           大众出版  \n",
       "4               出版           大众出版  "
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_all = df_all.rename(columns={'交易所': 'market','行业代码':'industrycode', '股票代码':'symbol','公司简称':'company_desc',\n",
    "               '新版一级行业':'industry_first','新版二级行业':'industry_seccond','新版三级行业':'industry_third'})\n",
    "df_all.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "None\n"
     ]
    }
   ],
   "source": [
    "#df = pd.DataFrame(columns=[ 'market','industrycode', 'symbol','company_desc',\n",
    "#              'industry_first','industry_seccond','industry_third' ])\n",
    "msg = df_all.to_sql(\n",
    "            name=\"stocks_202109\",\n",
    "            con=aliyun,\n",
    "            if_exists='replace',  #'fail'，'replace'，'append'}，默认'fail' \n",
    "          \n",
    "dtype={'market':sqlalchemy.types.NVARCHAR(length=5),\n",
    "       'industrycode':sqlalchemy.types.VARCHAR(6),\n",
    "       'symbol':sqlalchemy.types.VARCHAR(length=10),\n",
    "       'company_desc':sqlalchemy.types.NVARCHAR(length=50),\n",
    "       'industry_first':sqlalchemy.types.NVARCHAR(length=10),\n",
    "       'industry_seccond':sqlalchemy.types.NVARCHAR(length=20),\n",
    "       'industry_third':sqlalchemy.types.NVARCHAR(20)},\n",
    "            index=False)\n",
    "print(msg)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>area</th>\n",
       "      <th>industry</th>\n",
       "      <th>list_date</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ts_code</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>000001.SZ</th>\n",
       "      <td>平安银行</td>\n",
       "      <td>深圳</td>\n",
       "      <td>银行</td>\n",
       "      <td>19910403</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>000002.SZ</th>\n",
       "      <td>万科A</td>\n",
       "      <td>深圳</td>\n",
       "      <td>全国地产</td>\n",
       "      <td>19910129</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>000004.SZ</th>\n",
       "      <td>国华网安</td>\n",
       "      <td>深圳</td>\n",
       "      <td>软件服务</td>\n",
       "      <td>19910114</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>000005.SZ</th>\n",
       "      <td>ST星源</td>\n",
       "      <td>深圳</td>\n",
       "      <td>环境保护</td>\n",
       "      <td>19901210</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>000006.SZ</th>\n",
       "      <td>深振业A</td>\n",
       "      <td>深圳</td>\n",
       "      <td>区域地产</td>\n",
       "      <td>19920427</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           name area industry  list_date\n",
       "ts_code                                 \n",
       "000001.SZ  平安银行   深圳       银行   19910403\n",
       "000002.SZ   万科A   深圳     全国地产   19910129\n",
       "000004.SZ  国华网安   深圳     软件服务   19910114\n",
       "000005.SZ  ST星源   深圳     环境保护   19901210\n",
       "000006.SZ  深振业A   深圳     区域地产   19920427"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_tushare = pd.read_csv(stock_tushare)\n",
    "df_tushare =df_tushare.set_index('ts_code')\n",
    "df_tushare = df_tushare.drop(columns=['symbol'], axis=0)\n",
    "df_tushare.head()\n",
    "df_tushare.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>market</th>\n",
       "      <th>industrycode</th>\n",
       "      <th>company_desc</th>\n",
       "      <th>industry_first</th>\n",
       "      <th>industry_seccond</th>\n",
       "      <th>industry_third</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>symbol</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>000001.SZ</th>\n",
       "      <td>A股</td>\n",
       "      <td>480301</td>\n",
       "      <td>平安银行</td>\n",
       "      <td>银行</td>\n",
       "      <td>股份制银行Ⅱ</td>\n",
       "      <td>股份制银行Ⅲ</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>000002.SZ</th>\n",
       "      <td>A股</td>\n",
       "      <td>430101</td>\n",
       "      <td>万科A</td>\n",
       "      <td>房地产</td>\n",
       "      <td>房地产开发</td>\n",
       "      <td>住宅开发</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>000004.SZ</th>\n",
       "      <td>A股</td>\n",
       "      <td>710402</td>\n",
       "      <td>国华网安</td>\n",
       "      <td>计算机</td>\n",
       "      <td>软件开发</td>\n",
       "      <td>横向通用软件</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>000005.SZ</th>\n",
       "      <td>A股</td>\n",
       "      <td>760104</td>\n",
       "      <td>ST星源</td>\n",
       "      <td>环保</td>\n",
       "      <td>环境治理</td>\n",
       "      <td>综合环境治理</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>000006.SZ</th>\n",
       "      <td>A股</td>\n",
       "      <td>430101</td>\n",
       "      <td>深振业A</td>\n",
       "      <td>房地产</td>\n",
       "      <td>房地产开发</td>\n",
       "      <td>住宅开发</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          market  industrycode company_desc industry_first industry_seccond  \\\n",
       "symbol                                                                        \n",
       "000001.SZ     A股        480301         平安银行             银行           股份制银行Ⅱ   \n",
       "000002.SZ     A股        430101          万科A            房地产            房地产开发   \n",
       "000004.SZ     A股        710402         国华网安            计算机             软件开发   \n",
       "000005.SZ     A股        760104         ST星源             环保             环境治理   \n",
       "000006.SZ     A股        430101         深振业A            房地产            房地产开发   \n",
       "\n",
       "          industry_third  \n",
       "symbol                    \n",
       "000001.SZ         股份制银行Ⅲ  \n",
       "000002.SZ           住宅开发  \n",
       "000004.SZ         横向通用软件  \n",
       "000005.SZ         综合环境治理  \n",
       "000006.SZ           住宅开发  "
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_sw = pd.read_excel(stocks)\n",
    "df_sw = df_sw.rename(columns={'交易所': 'market','行业代码':'industrycode', '股票代码':'symbol','公司简称':'company_desc',\n",
    "               '新版一级行业':'industry_first','新版二级行业':'industry_seccond','新版三级行业':'industry_third'})\n",
    "df_sw = df_sw.set_index('symbol')\n",
    "df_sw = df_sw.sort_index()\n",
    "df_sw.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>area</th>\n",
       "      <th>industry</th>\n",
       "      <th>list_date</th>\n",
       "      <th>market</th>\n",
       "      <th>industrycode</th>\n",
       "      <th>company_desc</th>\n",
       "      <th>industry_first</th>\n",
       "      <th>industry_seccond</th>\n",
       "      <th>industry_third</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ts_code</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>000001.SZ</th>\n",
       "      <td>平安银行</td>\n",
       "      <td>深圳</td>\n",
       "      <td>银行</td>\n",
       "      <td>19910403</td>\n",
       "      <td>A股</td>\n",
       "      <td>480301.0</td>\n",
       "      <td>平安银行</td>\n",
       "      <td>银行</td>\n",
       "      <td>股份制银行Ⅱ</td>\n",
       "      <td>股份制银行Ⅲ</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>000002.SZ</th>\n",
       "      <td>万科A</td>\n",
       "      <td>深圳</td>\n",
       "      <td>全国地产</td>\n",
       "      <td>19910129</td>\n",
       "      <td>A股</td>\n",
       "      <td>430101.0</td>\n",
       "      <td>万科A</td>\n",
       "      <td>房地产</td>\n",
       "      <td>房地产开发</td>\n",
       "      <td>住宅开发</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>000004.SZ</th>\n",
       "      <td>国华网安</td>\n",
       "      <td>深圳</td>\n",
       "      <td>软件服务</td>\n",
       "      <td>19910114</td>\n",
       "      <td>A股</td>\n",
       "      <td>710402.0</td>\n",
       "      <td>国华网安</td>\n",
       "      <td>计算机</td>\n",
       "      <td>软件开发</td>\n",
       "      <td>横向通用软件</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>000005.SZ</th>\n",
       "      <td>ST星源</td>\n",
       "      <td>深圳</td>\n",
       "      <td>环境保护</td>\n",
       "      <td>19901210</td>\n",
       "      <td>A股</td>\n",
       "      <td>760104.0</td>\n",
       "      <td>ST星源</td>\n",
       "      <td>环保</td>\n",
       "      <td>环境治理</td>\n",
       "      <td>综合环境治理</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>000006.SZ</th>\n",
       "      <td>深振业A</td>\n",
       "      <td>深圳</td>\n",
       "      <td>区域地产</td>\n",
       "      <td>19920427</td>\n",
       "      <td>A股</td>\n",
       "      <td>430101.0</td>\n",
       "      <td>深振业A</td>\n",
       "      <td>房地产</td>\n",
       "      <td>房地产开发</td>\n",
       "      <td>住宅开发</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           name area industry  list_date market  industrycode company_desc  \\\n",
       "ts_code                                                                      \n",
       "000001.SZ  平安银行   深圳       银行   19910403     A股      480301.0         平安银行   \n",
       "000002.SZ   万科A   深圳     全国地产   19910129     A股      430101.0          万科A   \n",
       "000004.SZ  国华网安   深圳     软件服务   19910114     A股      710402.0         国华网安   \n",
       "000005.SZ  ST星源   深圳     环境保护   19901210     A股      760104.0         ST星源   \n",
       "000006.SZ  深振业A   深圳     区域地产   19920427     A股      430101.0         深振业A   \n",
       "\n",
       "          industry_first industry_seccond industry_third  \n",
       "ts_code                                                   \n",
       "000001.SZ             银行           股份制银行Ⅱ         股份制银行Ⅲ  \n",
       "000002.SZ            房地产            房地产开发           住宅开发  \n",
       "000004.SZ            计算机             软件开发         横向通用软件  \n",
       "000005.SZ             环保             环境治理         综合环境治理  \n",
       "000006.SZ            房地产            房地产开发           住宅开发  "
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = df_tushare.join(df_sw)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_csv(path+\"a_stocks.csv\", encoding='gbk')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>area</th>\n",
       "      <th>industry</th>\n",
       "      <th>list_date</th>\n",
       "      <th>market</th>\n",
       "      <th>company_desc</th>\n",
       "      <th>industry_first</th>\n",
       "      <th>industry_seccond</th>\n",
       "      <th>industry_third</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ts_code</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>000001.SZ</th>\n",
       "      <td>平安银行</td>\n",
       "      <td>深圳</td>\n",
       "      <td>银行</td>\n",
       "      <td>19910403</td>\n",
       "      <td>A股</td>\n",
       "      <td>平安银行</td>\n",
       "      <td>银行</td>\n",
       "      <td>股份制银行Ⅱ</td>\n",
       "      <td>股份制银行Ⅲ</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>000002.SZ</th>\n",
       "      <td>万科A</td>\n",
       "      <td>深圳</td>\n",
       "      <td>全国地产</td>\n",
       "      <td>19910129</td>\n",
       "      <td>A股</td>\n",
       "      <td>万科A</td>\n",
       "      <td>房地产</td>\n",
       "      <td>房地产开发</td>\n",
       "      <td>住宅开发</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>000004.SZ</th>\n",
       "      <td>国华网安</td>\n",
       "      <td>深圳</td>\n",
       "      <td>软件服务</td>\n",
       "      <td>19910114</td>\n",
       "      <td>A股</td>\n",
       "      <td>国华网安</td>\n",
       "      <td>计算机</td>\n",
       "      <td>软件开发</td>\n",
       "      <td>横向通用软件</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>000005.SZ</th>\n",
       "      <td>ST星源</td>\n",
       "      <td>深圳</td>\n",
       "      <td>环境保护</td>\n",
       "      <td>19901210</td>\n",
       "      <td>A股</td>\n",
       "      <td>ST星源</td>\n",
       "      <td>环保</td>\n",
       "      <td>环境治理</td>\n",
       "      <td>综合环境治理</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>000006.SZ</th>\n",
       "      <td>深振业A</td>\n",
       "      <td>深圳</td>\n",
       "      <td>区域地产</td>\n",
       "      <td>19920427</td>\n",
       "      <td>A股</td>\n",
       "      <td>深振业A</td>\n",
       "      <td>房地产</td>\n",
       "      <td>房地产开发</td>\n",
       "      <td>住宅开发</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           name area industry  list_date market company_desc industry_first  \\\n",
       "ts_code                                                                       \n",
       "000001.SZ  平安银行   深圳       银行   19910403     A股         平安银行             银行   \n",
       "000002.SZ   万科A   深圳     全国地产   19910129     A股          万科A            房地产   \n",
       "000004.SZ  国华网安   深圳     软件服务   19910114     A股         国华网安            计算机   \n",
       "000005.SZ  ST星源   深圳     环境保护   19901210     A股         ST星源             环保   \n",
       "000006.SZ  深振业A   深圳     区域地产   19920427     A股         深振业A            房地产   \n",
       "\n",
       "          industry_seccond industry_third  \n",
       "ts_code                                    \n",
       "000001.SZ           股份制银行Ⅱ         股份制银行Ⅲ  \n",
       "000002.SZ            房地产开发           住宅开发  \n",
       "000004.SZ             软件开发         横向通用软件  \n",
       "000005.SZ             环境治理         综合环境治理  \n",
       "000006.SZ            房地产开发           住宅开发  "
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "table = data.sheets()[0]          #通过索引顺序获取\n",
    "\n",
    "table = data.sheet_by_index(sheet_indx)) #通过索引顺序获取\n",
    "\n",
    "table = data.sheet_by_name(sheet_name)#通过名称获取\n",
    "\n",
    "以上三个函数都会返回一个xlrd.sheet.Sheet()对象\n",
    "\n",
    "names = data.sheet_names()    #返回book中所有工作表的名字\n",
    "\n",
    "data.sheet_loaded(sheet_name or indx)   # 检查某个sheet是否导入完毕\n",
    "\n",
    "\n",
    "nrows = table.nrows  #获取该sheet中的有效行数\n",
    "\n",
    "table.row(rowx)  #返回由该行中所有的单元格对象组成的列表\n",
    "\n",
    "table.row_slice(rowx)  #返回由该列中所有的单元格对象组成的列表\n",
    "\n",
    "table.row_types(rowx, start_colx=0, end_colx=None)    #返回由该行中所有单元格的数据类型组成的列表\n",
    "\n",
    "table.row_values(rowx, start_colx=0, end_colx=None)   #返回由该行中所有单元格的数据组成的列表\n",
    "\n",
    "table.row_len(rowx) #返回该列的有效单元格长度\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>area</th>\n",
       "      <th>industry</th>\n",
       "      <th>list_date</th>\n",
       "      <th>market</th>\n",
       "      <th>company_desc</th>\n",
       "      <th>industry_first</th>\n",
       "      <th>industry_seccond</th>\n",
       "      <th>industry_third</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ts_code</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>000001.SZ</th>\n",
       "      <td>平安银行</td>\n",
       "      <td>深圳</td>\n",
       "      <td>银行</td>\n",
       "      <td>19910403</td>\n",
       "      <td>A股</td>\n",
       "      <td>平安银行</td>\n",
       "      <td>银行</td>\n",
       "      <td>股份制银行Ⅱ</td>\n",
       "      <td>股份制银行Ⅲ</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>000002.SZ</th>\n",
       "      <td>万科A</td>\n",
       "      <td>深圳</td>\n",
       "      <td>全国地产</td>\n",
       "      <td>19910129</td>\n",
       "      <td>A股</td>\n",
       "      <td>万科A</td>\n",
       "      <td>房地产</td>\n",
       "      <td>房地产开发</td>\n",
       "      <td>住宅开发</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>000004.SZ</th>\n",
       "      <td>国华网安</td>\n",
       "      <td>深圳</td>\n",
       "      <td>软件服务</td>\n",
       "      <td>19910114</td>\n",
       "      <td>A股</td>\n",
       "      <td>国华网安</td>\n",
       "      <td>计算机</td>\n",
       "      <td>软件开发</td>\n",
       "      <td>横向通用软件</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>000005.SZ</th>\n",
       "      <td>ST星源</td>\n",
       "      <td>深圳</td>\n",
       "      <td>环境保护</td>\n",
       "      <td>19901210</td>\n",
       "      <td>A股</td>\n",
       "      <td>ST星源</td>\n",
       "      <td>环保</td>\n",
       "      <td>环境治理</td>\n",
       "      <td>综合环境治理</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>000006.SZ</th>\n",
       "      <td>深振业A</td>\n",
       "      <td>深圳</td>\n",
       "      <td>区域地产</td>\n",
       "      <td>19920427</td>\n",
       "      <td>A股</td>\n",
       "      <td>深振业A</td>\n",
       "      <td>房地产</td>\n",
       "      <td>房地产开发</td>\n",
       "      <td>住宅开发</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           name area industry  list_date market company_desc industry_first  \\\n",
       "ts_code                                                                       \n",
       "000001.SZ  平安银行   深圳       银行   19910403     A股         平安银行             银行   \n",
       "000002.SZ   万科A   深圳     全国地产   19910129     A股          万科A            房地产   \n",
       "000004.SZ  国华网安   深圳     软件服务   19910114     A股         国华网安            计算机   \n",
       "000005.SZ  ST星源   深圳     环境保护   19901210     A股         ST星源             环保   \n",
       "000006.SZ  深振业A   深圳     区域地产   19920427     A股         深振业A            房地产   \n",
       "\n",
       "          industry_seccond industry_third  \n",
       "ts_code                                    \n",
       "000001.SZ           股份制银行Ⅱ         股份制银行Ⅲ  \n",
       "000002.SZ            房地产开发           住宅开发  \n",
       "000004.SZ             软件开发         横向通用软件  \n",
       "000005.SZ             环境治理         综合环境治理  \n",
       "000006.SZ            房地产开发           住宅开发  "
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>area</th>\n",
       "      <th>industry</th>\n",
       "      <th>list_date</th>\n",
       "      <th>market</th>\n",
       "      <th>company_desc</th>\n",
       "      <th>industry_first</th>\n",
       "      <th>industry_seccond</th>\n",
       "      <th>industry_third</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ts_code</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>600611.SH</th>\n",
       "      <td>大众交通</td>\n",
       "      <td>上海</td>\n",
       "      <td>公共交通</td>\n",
       "      <td>19920807</td>\n",
       "      <td>A股</td>\n",
       "      <td>大众交通</td>\n",
       "      <td>交通运输</td>\n",
       "      <td>铁路公路</td>\n",
       "      <td>公交</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>601000.SH</th>\n",
       "      <td>唐山港</td>\n",
       "      <td>河北</td>\n",
       "      <td>港口</td>\n",
       "      <td>20100705</td>\n",
       "      <td>A股</td>\n",
       "      <td>唐山港</td>\n",
       "      <td>交通运输</td>\n",
       "      <td>航运港口</td>\n",
       "      <td>港口</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>600279.SH</th>\n",
       "      <td>重庆港九</td>\n",
       "      <td>重庆</td>\n",
       "      <td>港口</td>\n",
       "      <td>20000731</td>\n",
       "      <td>A股</td>\n",
       "      <td>重庆港九</td>\n",
       "      <td>交通运输</td>\n",
       "      <td>航运港口</td>\n",
       "      <td>港口</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>600548.SH</th>\n",
       "      <td>深高速</td>\n",
       "      <td>深圳</td>\n",
       "      <td>路桥</td>\n",
       "      <td>20011225</td>\n",
       "      <td>A股</td>\n",
       "      <td>深高速</td>\n",
       "      <td>交通运输</td>\n",
       "      <td>铁路公路</td>\n",
       "      <td>高速公路</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>000507.SZ</th>\n",
       "      <td>珠海港</td>\n",
       "      <td>广东</td>\n",
       "      <td>港口</td>\n",
       "      <td>19930326</td>\n",
       "      <td>A股</td>\n",
       "      <td>珠海港</td>\n",
       "      <td>交通运输</td>\n",
       "      <td>航运港口</td>\n",
       "      <td>港口</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           name area industry  list_date market company_desc industry_first  \\\n",
       "ts_code                                                                       \n",
       "600611.SH  大众交通   上海     公共交通   19920807     A股         大众交通           交通运输   \n",
       "601000.SH   唐山港   河北       港口   20100705     A股          唐山港           交通运输   \n",
       "600279.SH  重庆港九   重庆       港口   20000731     A股         重庆港九           交通运输   \n",
       "600548.SH   深高速   深圳       路桥   20011225     A股          深高速           交通运输   \n",
       "000507.SZ   珠海港   广东       港口   19930326     A股          珠海港           交通运输   \n",
       "\n",
       "          industry_seccond industry_third  \n",
       "ts_code                                    \n",
       "600611.SH             铁路公路             公交  \n",
       "601000.SH             航运港口             港口  \n",
       "600279.SH             航运港口             港口  \n",
       "600548.SH             铁路公路           高速公路  \n",
       "000507.SZ             航运港口             港口  "
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df=df.sort_values('industry_first')\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
